#!/usr/bin/python
#coding=utf-8

from model.dbutils import DBUtils
import MySQLdb
from igor import logging


class Product(object):
    
    def __init__(self, pid = None, name = None, price = None, 
            descpt = None, img = None, uid = None, pdate = None):
        self.pid = pid 
        self.name = name
        self.price = price
        self.descpt = descpt
        self.img = img
        self.uid = uid
        self.pdate = pdate 


class ProductManager(object):

    def __init__(self):
        self.dbutils = DBUtils()

    def insert(self, product):
        conn, cur = None, None
        sql_product = """
            INSERT INTO product(name, price, descpt, img, uid)
            VALUES(%s, %s, %s, %s, %s)
            """
        sql_tags = "insert into tags values(%s, %s)"
        params = (product.name, product.price, product.descpt, product.img, product.uid)
        try:
            conn = self.dbutils.connect()
            cur = conn.cursor()
            cur.execute(sql_product, params)
            values = []
            pid = conn.insert_id()
            if pid:
                for tag in product.tags:
                    values.append((pid, tag))
                if len(values) > 0:
                    cur.executemany(sql_tags, values)
            conn.commit()
        except MySQLdb.Error, err:
            conn.rollback()
            logging.log(err)
        finally:
            self.dbutils.close(conn, cur)
                
    ##根据商品id查询商品详细信息
    def query_by_id(self, pid):
        sql = """
            SELECT id, uid, name, price, descpt, pdate,
            GROUP_CONCAT(tag SEPARATOR '|') AS tags
            FROM product LEFT JOIN tags
            ON product.id = tags.pid WHERE id=%s GROUP BY id ;
            """
        rows = self.dbutils.execute_dict(sql, pid)
        if rows:
            row = rows[0]
            product = Product()
            product.pid = pid 
            product.uid = row['uid']
            product.name = row['name']
            product.price = row['price']
            product.descpt = row['descpt']
            product.pdate = row['pdate']
            product.tags = row['tags']
            return product 
        else:
            return None
           
    #查询商品图片
    def query_img(self, pid):
        img = None
        sql = """
            SELECT img FROM product WHERE id=%s
            """
        rows = self.dbutils.execute_dict(sql, pid)
        if rows:
            row = rows[0]
            img = row['img']
        return img 

    def query(self):
        sql = """
            SELECT id, uid, name, price, descpt, 
            GROUP_CONCAT(tag SEPARATOR '|') AS tags
            FROM product LEFT JOIN tags
            ON product.id = tags.pid GROUP BY id ;
            """
        rows = self.dbutils.execute_dict(sql)
        items = []
        if rows:
            for row in rows:
                product = Product()
                product.pid = row['id']
                product.uid = row['uid']
                product.name = row['name']
                product.price = row['price']
                product.descpt = row['descpt']
                product.tags = row['tags']
                items.append(product)
        return items 

    ##分页查询
    def query_page(self, start, limit):
        sql = """
            SELECT id, uid, name, price, descpt, 
            GROUP_CONCAT(tag SEPARATOR '|') AS tags
            FROM product LEFT JOIN tags
            ON product.id = tags.pid WHERE product.status=0 
            GROUP BY id LIMIT %s, %s;
            """
        params = (start, limit)
        rows = self.dbutils.execute_dict(sql, params)
        items = []
        if rows:
            for row in rows:
                product = Product()
                product.pid = row['id']
                product.uid = row['uid']
                product.name = row['name']
                product.price = row['price']
                product.descpt = row['descpt']
                product.tags = row['tags']
                items.append(product)
        logging.log('items size=%s' % len(items))
        return items 

    def count(self):
        sql = 'SELECT COUNT(*) AS cnt FROM product WHERE status=0'
        rows = self.dbutils.execute_dict(sql)
        cnt = 0
        if rows:
            cnt = rows[0]['cnt']
        return cnt

    def query_items(self, uid):
        sql = """
            SELECT id, name, price, descpt, status, pdate 
            FROM product WHERE uid = %s
            """
        rows = self.dbutils.execute_dict(sql, uid)
        items = []
        if rows:
            for row in rows:
                product = Product()
                product.pid = row['id']
                product.name = row['name']
                product.price = row['price']
                product.descpt = row['descpt']
                product.status = row['status']
                product.pdate = row['pdate']
                items.append(product)
        return items

   
